IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_Report_InspectionsByWeekAndContact')
	BEGIN
		DROP  Procedure  up_Report_InspectionsByWeekAndContact
	END

GO

CREATE Procedure up_Report_InspectionsByWeekAndContact
	@ProjectId int = null
AS

--HANDLE OLD VERSION
IF (@ProjectId IS NULL)
BEGIN
	SET @ProjectId = 6
END



select
	 
	VC.Name AS Person, 
	DATEPART(wk, V.SatelliteTime) AS weekNumber, 
	1 AS numberVisit, 
	convert(nvarchar,dbo.fn_GetFirstDateofWeek(V.SatelliteTime),110) as FirstWeekDate,
	convert(nvarchar,dbo.fn_GetLastDateofWeek(V.SatelliteTime),110) as LastWeekDate,
	(select
		count(P.Vendor_InspVisit_XMissionStructureFk) 
			from tb_Vendor_InspVisit_XmissionStructures_Poles P 
			where P.Vendor_InspVisit_XMissionStructureFk = V.Vendor_InspVisit_XMissionStructureId
	) as CountPoles
 from 
	tb_Vendor_InspVisit_XMissionStructures V 
	INNER JOIN tb_Vendor_Contacts AS VC ON VC.Vendor_ContactId = V.Vendor_ContactFK
	--LEFT JOIN tb_Utility_Contacts AS VC ON VC.Utility_ContactId = V.Vendor_ContactFK
WHERE 
	Vendor_ProjectFK = @ProjectId
order by DATEPART(wk, V.SatelliteTime)



GO

GRANT EXEC ON up_Report_InspectionsByWeekAndContact TO PPIReports

GO

